import MySQLdb



# table name : fundHoldings

# 打开数据库连接
db = MySQLdb.connect("localhost", "jijin", "jijin", "jijin", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
try:
   # 执行sql语句
   cursor.execute('INSERT INTO fundHoldings \
       (fundId, years, stockId, stockName, percentage, numbers, marketValue) \
        VALUES \
            (%s, %s, %s, %s, %s, %s, %s)')
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# 关闭数据库连接
db.close()


# 封装打开数据库连接方法
def getConnMySQL():
  db = MySQLdb.connect("localhost", "jijin", "jijin", "jijin", charset='utf8' )
  return db

# 封装关闭数据库连接方法
def closeConnMySQL(db):
  # 关闭数据库连接
  db.close()



# 提供一个DB对接接口，可以任意执行SQL
def executeSelectSQL(executeSQL, par):
    records = []
    db = getConnMySQL()
    cursor = db.cursor()
    try:
      cursor.execute(executeSQL, par)
    # 获取所有记录列表
      results = cursor.fetchall()
      for row in results:
        i = 0
        c = len(row)
        # print("ssss: " + str(row))
        record = []
        while i < c:
          record.append(row[i])
          i += 1
        
        records.append(record.copy())
    except Exception as e:
      print("出现如下异常：%s" % e)

    closeConnMySQL(db)
    return records


def executeUpdateSQL(executeSQL, records):
    db = getConnMySQL()
    cursor = db.cursor()

    for par in records:
        # print(par)
        try:
            cursor.execute(executeSQL, par)
            db.commit()
        except Exception as e:
            db.rollback()
            print("出现如下异常：%s" % e)


    closeConnMySQL(db)



# 初始化基金基金清单
def initFundTable(records):
    db = getConnMySQL()
    cursor = db.cursor()

    print("开始初始化基金信息，本次更新初始化数量：%s" % (len(records)))

    for record in records:
      
        sql = "INSERT INTO fund (fundId, fundName, type1) VALUES (%s, %s, %s)"
        par = (record[0], record[2], record[3])

        try:
            cursor.execute(sql, par)
            db.commit()
        except Exception as e:
            db.rollback()
            print(e)


    closeConnMySQL(db)

    print("初始化基金基金清单！")



# 更新所有年份指定基金净值
def updateFundValue(fundId, records):
    db = MySQLdb.connect("localhost", "jijin", "jijin", "jijin", charset='utf8' )

    cursor = db.cursor()

    delSQL = "DELETE FROM jjjz WHERE fundId = '%s'"
    delPar = (fundId)
    try:
        cursor.execute(delSQL, delPar)
        db.commit()
    except:
        db.rollback()


    for record in records:

        print("更新进度：代码： %s, 日期：%s， 净值：%s， 率：%s" % (fundId, record['Date'], record['NetAssetValue'], record['ChangePercent']))

        sql = "INSERT INTO jjjz (fundId, FSRQ, DWJZ, JZZZL) VALUES (%s, %s, %s, %s)"
        par = (record['Code'], record['Date'], record['NetAssetValue'], record['ChangePercent'])

        
        try:
            cursor.execute(sql, par)
            db.commit()
        except Exception as e:
            db.rollback()
            print(e)

    db.close()

    print("%s基金净值更新结束！" % (fundId))


# 更新当年份指定基金净值
def updateCurYearFundValue(fundId, records):
    db = MySQLdb.connect("localhost", "jijin", "jijin", "jijin", charset='utf8' )

    cursor = db.cursor()

    delSQL = "DELETE FROM jjjz WHERE fundId = '%s' AND DATE_FORMAT(FSRQ, '%Y') = '2021'"
    delPar = (fundId)
    try:
        cursor.execute(delSQL, delPar)
        db.commit()
    except:
        db.rollback()


    for record in records:

        print("更新进度：日期：%s， 净值：%s" % (record['Date'], record['NetAssetValue']))

        sql = "INSERT INTO jjjz (fundId, FSRQ, DWJZ, JZZZL) VALUES (%s, %s, %s, %s)"
        par = (record['Code'], record['Date'], record['NetAssetValue'], record['ChangePercent'])

        
        try:
            cursor.execute(sql, par)
            db.commit()
        except:
            db.rollback()

    db.close()

    print("%s基金净值更新结束！" % (fundId))


# 获取所有基金信息，组成元组
# 也可以通过指定基金ID获取
def getAllFunds(fundId):
  records = []
  record = {}

  db = getConnMySQL()
  cursor = db.cursor()

  # SQL 查询语句
  # sql = 'SELECT * FROM Fund'
  # sql = "SELECT fundId FROM fund WHERE fundId NOT IN (SELECT DISTINCT fundId FROM jjjz) AND fundName NOT LIKE '%后端%'"
  # 更新某个类型的基金
  # sql = "SELECT fundId FROM fund WHERE 1=1 AND type1='股票指数'"
  # 更新当天基金净值SQL
  # SELECT * FROM fund WHERE fundId NOT IN (SELECT fundId FROM jjjz WHERE FSRQ = DATE_FORMAT(NOW(), '%Y-%m-%d'));
  sql = "SELECT * FROM fund WHERE 1=1 AND fundId NOT IN (SELECT DISTINCT fundId FROM jjjz WHERE FSRQ = DATE_FORMAT('2021-02-26', '%Y-%m-%d')) AND fundName NOT LIKE '%后端%'"
  if fundId != "":
    sql += " AND fundId='%s'" % (fundId)

    print(sql)

  try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        fundId = row[0]
        # fundName = row[1]
        # type1 = row[5]
        record['Code'] = fundId
        records.append(record.copy())
        # 打印结果
        # print ("fundId=%s,fundName=%s,type1=%s" % (fundId, fundName, type1))
        
  except Exception as ex:
    db.rollback()
    print(e)
    print("出现如下异常%s"%ex)

  closeConnMySQL(db)

  return records

# 更新前一天基金净值
def updateDayFundValue(fundId, records):
    db = getConnMySQL()
    cursor = db.cursor()

    for record in records:

        print("更新进度：基金代码：%s， 日期：%s，幅度：%s， 净值：%s" % (fundId, record['Date'], record['ChangePercent'], record['NetAssetValue']))

        sql = "UPDATE jjjz SET DWJZ=%s, JZZZL=%s WHERE fundId=%s AND FSRQ=%s"
        par = (record['NetAssetValue'], record['ChangePercent'], record['Code'], record['Date'])
        try:
            rs = cursor.execute(sql, par)
            if rs == 0:
              #  如果没有当天估值就插入一条记录
              sql = "INSERT INTO jjjz (fundId, FSRQ, DWJZ, JZZZL) VALUES (%s, %s, %s, %s)"
              par = (record['Code'], record['Date'], record['NetAssetValue'], record['ChangePercent'])
              cursor.execute(sql, par)

            db.commit()
        except Exception as e:
            db.rollback()
            print(e)
            print("出现如下异常%s"%e)

    closeConnMySQL(db)

    # print("%s基金净值更新结束！" % (fundId))


# 更新基金持仓情况
def updateFundStock(fundId, records):
    db = getConnMySQL()
    cursor = db.cursor()

    for record in records:

        print("更新内容：季度：%s， 股票：%s" % (record[5], record[1]))

        sql = "INSERT INTO fundHoldings (fundId, years, stockId, stockName, \
            percentage, numbers, marketValue, year) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
        # print(tds)
    #   161725, 2020-4, tds[1].text, tds[2].text, tds[2].text, tds[4].text, tds[5].text, tds[6].text

        par = (fundId, record[5], record[0], record[1], record[2], record[3], record[4], record[5][0:4])
        print(par)
        try:
            cursor.execute(sql, par)
            db.commit()
        except Exception as e:
            db.rollback()
            print(e)


    closeConnMySQL(db)

    # print("%s基金持仓更新结束！" % (fundId))


# Main方法
# if __name__ == "__main__":

  # print(getAllFunds(""))






# 用于对基金当天的估值进行获取

def getAllFundsD(fundId):
  records = []
  record = {}

  db = getConnMySQL()
  cursor = db.cursor()

  # SQL 查询语句
  sql = "SELECT fundId FROM fund WHERE 1=1 AND fundId NOT IN (SELECT DISTINCT fundId FROM jjjz WHERE 1=1 AND FSRQ=DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y-%m-%d') AND gztime = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y-%m-%d')) AND fundName NOT LIKE '%后端%'"
  if fundId != "":
    sql += " AND fundId='%s'" % (fundId)

    print(sql)

  try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        fundId = row[0]
        # fundName = row[1]
        # type1 = row[5]
        record['Code'] = fundId
        records.append(record.copy())
        # 打印结果
        # print ("fundId=%s,fundName=%s,type1=%s" % (fundId, fundName, type1))
        
  except Exception as ex:
    db.rollback()
    print(e)
    print("出现如下异常%s"%ex)

  closeConnMySQL(db)

  return records

# 插入当天基金估值
def insertTodayFundGZ(fundId, records):
    db = getConnMySQL()
    cursor = db.cursor()

    for record in records:

        print("更新进度：基金代码：%s， 估值日期：%s，估值涨幅：%s， 估值：%s" % (fundId, record['gztime'], record['gszzl'], record['gsz']))

        sql = "INSERT INTO jjjz (fundId, gztime, gszzl, gsz) VALUES (%s, %s, %s, %s)"
        par = (record['Code'], record['gztime'], record['gszzl'], record['gsz'])

        try:
            cursor.execute(sql, par)
            db.commit()
        except Exception as e:
            db.rollback()
            print(e)
            print("出现如下异常%s"%e)

    closeConnMySQL(db)

# 更新当天基金估值
def updateTodayFundGZ(fundId, records):
    db = getConnMySQL()
    cursor = db.cursor()

    for record in records:

        print("更新进度：基金代码：%s， 估值日期：%s，估值涨幅：%s， 估值：%s" % (fundId, record['gztime'], record['gszzl'], record['gsz']))

        sql = "UPDATE jjjz SET gztime=%s, gszzl=%s, gsz=%s WHERE fundId=%s AND FSRQ=%s"
        par = (record['gztime'], record['gszzl'], record['gsz'], record['Code'], record['gztime'])
        try:
            rs = cursor.execute(sql, par)
            if rs == 0:
              #  如果没有当天估值就插入一条记录
              sql = "INSERT INTO jjjz (fundId, FSRQ, gszzl, gsz, gztime) VALUES (%s, %s, %s, %s, %s)"
              par = (record['Code'], record['gztime'], record['gszzl'], record['gsz'], record['gztime'])
              cursor.execute(sql, par)

            db.commit()
        except Exception as e:
            db.rollback()
            print(e)
            print("出现如下异常%s"%e)

    closeConnMySQL(db)

# 自定义获取基金SQL
def getDiyFunds(fundSQL):
  records = []
  record = {}

  db = getConnMySQL()
  cursor = db.cursor()

  # SQL 查询语句
  # print(fundSQL)

  try:
    # 执行SQL语句
    cursor.execute(fundSQL)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        fundId = row[0]
        # fundName = row[1]
        # type1 = row[5]
        record['Code'] = fundId
        records.append(record.copy())
        # 打印结果
        # print ("fundId=%s,fundName=%s,type1=%s" % (fundId, fundName, type1))
        
  except Exception as ex:
    db.rollback()
    print(e)
    print("出现如下异常%s"%ex)

  closeConnMySQL(db)

  return records

# 更新基金季度换手率
def updateFundHSL(fundId, records):
    db = getConnMySQL()
    cursor = db.cursor()

    for record in records:

        print("更新进度：基金代码：%s， 日期：%s，换手率：%s" % (fundId, record['REPORTDATE'], record['STOCKTURNOVER']))

        sql = "INSERT INTO jjhsl (fundId, REPORTDATE, STOCKTURNOVER) VALUES (%s, %s, %s)"
        par = (record['Code'], record['REPORTDATE'], record['STOCKTURNOVER'])

        try:
            cursor.execute(sql, par)
            db.commit()
        except Exception as e:
            db.rollback()
            print(e)
            print("出现如下异常%s"%e)

    closeConnMySQL(db)





# 自定义SQL获取数据
def getDataBySQL(fundSQL):
  records = []
  

  db = getConnMySQL()
  cursor = db.cursor()

  # SQL 查询语句
  # print(fundSQL)

  try:
    # 执行SQL语句
    cursor.execute(fundSQL)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
      record = []
      size = len(row)
      i = 0
      while(i < size):
        record.append(str(row[i]))
        i += 1

      records.append(record)
        # 打印结果
        
  except Exception as ex:
    db.rollback()
    print(e)
    print("出现如下异常%s"%ex)

  closeConnMySQL(db)

  return records

